{
 "metadata": {
  "name": "",
  "signature": "sha256:d60886187a90cc2a8aa5dfd191059d0390bbf8bef519cb8bc738e60544a74c50"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "##load the libraries\n",
      "import pandas as pd\n",
      "from django.db.models import Q\n",
      "##load the django model\n",
      "from twitterSentiment.models import Company, CompanyFinancials, TwitterText, CompanyAltmanZscore, CompanySentiment"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def company_sentiment_calculations(companyid):\n",
      "    try:\n",
      "        company_sentiments = CompanySentiment.objects.filter(company_id=companyid).values()\n",
      "#        print (company_sentiments.count())\n",
      "        if company_sentiments.count() >0:\n",
      "            company_sentiments_list = pd.DataFrame(list(company_sentiments))\n",
      "            averages = pd.Series([company_sentiments_list['sentiment_prob_very_negative'].astype(float).sum(),\n",
      "                company_sentiments_list['sentiment_prob_negative'].astype(float).sum(),\n",
      "                company_sentiments_list['sentiment_prob_neutral'].astype(float).sum(),\n",
      "                company_sentiments_list['sentiment_prob_positive'].astype(float).sum(),\n",
      "                company_sentiments_list['sentiment_prob_very_positive'].astype(float).sum()],\n",
      "                index=[0,1,2,3,4])\n",
      "            return (averages.idxmax(axis=1)) #pick the index with the maximum probability\n",
      "        else:\n",
      "            return \n",
      "    except:\n",
      "        print (\"sentiment error occured for company:\", companyid, \"error:\",sys.exc_info())\n",
      "        return (-1)\n",
      "\n",
      "    \n",
      "def company_zscore_calculations(companyid):\n",
      "    try:\n",
      "        company_zscore = CompanyAltmanZscore.objects.all().filter(company_id=companyid).values('zscore')\n",
      "        r = list(company_zscore[:1])\n",
      "        if r:\n",
      "            return r[0]['zscore']\n",
      "        return \n",
      "\n",
      "    except:\n",
      "        print (\"zscore error occured for company:\", companyid, \"error:\",sys.exc_info())\n",
      "        return (-1)\n",
      "                   "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 193
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "##get the companies from the database\n",
      "companylist = pd.DataFrame(list(Company.objects.filter(~Q(marketCap=0.0)).values()))\n",
      "print(\"total companies listed\",companylist.count())\n",
      "companylist['sentiment']=companylist[['id']].apply(company_sentiment_calculations, axis=1)\n",
      "companylist['altman']=companylist[['id']].apply(company_zscore_calculations, axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "total companies listed adrTso            5787\n",
        "date_extracted    5787\n",
        "exchange          5787\n",
        "id                5787\n",
        "industry          5787\n",
        "ipoYear           5787\n",
        "lastSale          5787\n",
        "marketCap         5787\n",
        "name              5787\n",
        "sector            5787\n",
        "summaryQuote      5787\n",
        "symbol            5787\n",
        "dtype: int64\n"
       ]
      }
     ],
     "prompt_number": 169
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "companylistix = companylist.set_index(['id'])\n",
      "companylistix.to_csv(\"companysentimentszscore.csv\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 213
    }
   ],
   "metadata": {}
  }
 ]
}